using System.Collections.Generic;
using System.Data;
using Lab1;
using Lab3MiddleTier.Model;

namespace Lab3MiddleTier
{
	public class MiddleTier
	{
		private Backend _databas;

		public MiddleTier(string connectionstring)
		{
			_databas = new Backend(connectionstring);
		}

		public IEnumerable<UserFile> GetAllFiles()
		{
			List<UserFile> files = new List<UserFile>();

			var dfiles = _databas.GetData("select * from userfile;");

			for (int i = 0; i < dfiles.Rows.Count;i++ )
			{
				files.Add(DataRowToUserfile(dfiles.Rows[i]));
			}
			
			return files;
		}

		public UserFile GetFileById(int id)
		{
			return DataRowToUserfile(_databas.GetData(string.Format("select * from userfile where id={0};", id)).Rows[0]);
		}

		public User GetUserById(int id)
		{
			var user = _databas.GetData(string.Format("select * from user where id={0}",id));

			return DataTableToUser(user);
		}

		public IEnumerable<UserFile> GetFilesBySearch(string searchWord)
		{
			List<UserFile> files = new List<UserFile>();

			var dfiles = _databas.GetData(string.Format("select * from userfile where name like '%{0}%'",searchWord));

			for(int i=0;i<dfiles.Rows.Count;i++)
			{
				files.Add(DataRowToUserfile(dfiles.Rows[i]));
			}

			

			return files;
		}


		public bool AddFile(UserFile userfile)
		{
			Dictionary<string, object> datas = new Dictionary<string, object>();
			datas.Add("uid", userfile.Owner.ID);
			datas.Add("name", userfile.Name);
			datas.Add("size", userfile.Size.ToString());
			datas.Add("type", userfile.Type);
			datas.Add("file", userfile.FileBytes);

			return _databas.InsertData("userfile", datas);
		}

		public bool RemoveFileByID(int id)
		{
			return _databas.DeleteData(string.Format("delete from userfile where id={0};", id)); 
		}

		public int GetNumberOfFiles()
		{
			var dfiles = _databas.GetData("select * from userfile;");
			return dfiles.Rows.Count;
		}

		public User GetUserByUsername(string username)
		{
			var user = _databas.GetData("select * from user where username='" + username + "';");
			return DataTableToUser(user);
		}

		public UserFile DataRowToUserfile(DataRow dr)
		{
			
			var uf = new UserFile();
			uf.ID = (int)dr["id"];
			uf.Name = (string)dr["name"];
			uf.Owner = GetUserById((int)dr["uid"]);
			uf.FileBytes = (byte[])dr["file"];
			uf.Size = int.Parse((string)dr["size"]);
			uf.Type = (string)dr["type"];

			return uf;
		}

		public User DataTableToUser(DataTable dt)
		{
			User u = new User();

			u.ID = (int)dt.Rows[0]["id"];
			u.Username = (string)dt.Rows[0]["username"];
			u.Password = (string)dt.Rows[0]["password"];
			u.Email = (string)dt.Rows[0]["email"];
			u.Banned = (bool)dt.Rows[0]["banned"];
			u.Role = (int)dt.Rows[0]["role"];

			return u;
		}

		public bool AddUser(User user)
		{
			
			var data = new Dictionary<string, object>
			           	{
			           		{"username", user.Username},
			           		{"password", user.Password},
			           		{"email", user.Email},
			           		{"banned", 0},
			           		{"role", 0}
			           	}; 

			_databas.InsertData("user", data);

			return true;
		}

	}
}